package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import www.lagou.entity.Account;
import www.lagou.entity.Employee;
import www.lagou.entity.Phone;
import www.lagou.entity.Transaction;
import www.lagou.utils.DateUtils;
import www.lagou.utils.DruidUtils;
import java.sql.SQLException;
import java.util.List;

/**
 * @author cyt
 * @date 2020/7/12
 */

public class test {

    @Test
    /**
     * 按照步骤 实现卡号：1122334455向55443332211转账5000元的操作；
     * a) 使用连接池创建QueryRunner对象；
     * b) 判断转出方是否有足够余额，如果不足，提示信息：”余额不足！”，并结束程序；
     * c) 通过卡号 进行转账的操作；
     * d) 转账结束后, 将转入、转出记录分别写入到Transaction表中
     */
    public void test_01() throws SQLException {
        //使用DBUtils,用的时druid的连接池
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //查询转出账号的余额
        String sql1 = "select * from account where card = ?";

        Account account = queryRunner.query(sql1, new BeanHandler<Account>(Account.class), "1122334455");

        if(account.getBalance() >= 5000){
            //转账
            String sql2 = "UPDATE account SET balance = balance - 5000 WHERE card = '1122334455'";
            int update1 = queryRunner.update(sql2);
            if(update1 == 1){
                System.out.println("转出成功！");
            }else {
                System.out.println("转出失败！");
            }
            String sql3 = "UPDATE account SET balance = balance + 5000 WHERE card = '55443332211'";
            int update2 = queryRunner.update(sql3);
            if(update1 == 1){
                System.out.println("转入成功！");
            }else {
                System.out.println("转入失败！");
            }

            //记录交易
            String datetime = DateUtils.getDateFormart();
            String sql4 = "INSERT INTO transaction VALUES(?,?,?,?,?)";
            //记录转出交易
            Transaction transaction1 = new Transaction(null,"1122334455","转出",5000D, datetime);
            int update3 = queryRunner.update(sql4, transaction1.getId(), transaction1.getCardid(), transaction1.getTratype(), transaction1.getTramoney(), transaction1.getTradate());
            if(update3 == 1){
                System.out.println("转出记录保存成功！");
            }else {
                System.out.println("转出记录保存失败！");
            }
            //记录转入交易
            Transaction transaction2 = new Transaction(null, "55443332211","转入",5000D,datetime);
            int update4 = queryRunner.update(sql4,transaction2.getId(),transaction2.getCardid(),transaction2.getTratype(),transaction2.getTramoney(),datetime);
            if(update4 == 1){
                System.out.println("转入记录保存成功！");
            }else {
                System.out.println("转入记录保存失败！");
            }


        }else {
            System.out.println("余额不足！");
            System.exit(1);
        }


    }

    @Test
    /**
     * 需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
     *
     * 需求2:  查询所有颜色是白色的手机信息
     *
     * 编程题3
     */
    public void test_02() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //查询价格高于2000元，生产日期是2019年之前的所有手机
        String sql1 = "SELECT * FROM phone WHERE price > 2000 AND year(prodate) < 2019";

        List<Phone> phones = queryRunner.query(sql1, new BeanListHandler<Phone>(Phone.class));
        //展示查到的手机的信息
        System.out.println("价格高于2000元，生产日期是2019年之前的所有手机为：");
        for (Phone p : phones){
            System.out.println(p.toString());
        }

        System.out.println("---------------------------------------");

        System.out.println("所有颜色是白色的手机信息:");
        //查询所有颜色是白色的手机信息
        String sql2 = "SELECT * FROM phone WHERE color = '白色'";
        List<Phone> phoneList = queryRunner.query(sql2, new BeanListHandler<Phone>(Phone.class));
        //展示查到的手机的信息
        for (Phone p : phoneList){
            System.out.println(p.toString());
        }

    }

    @Test
    /**
     * 需求1: 查询所有的员工信息 (不包含没有部门的员工)。
     *
     * 需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
     */
    public void test_03() throws SQLException {

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //查询所有的员工信息 (不包含没有部门的员工)。
        String sql1 = "SELECT * FROM employee WHERE did IS NOT null";
        List<Employee> employees = queryRunner.query(sql1, new BeanListHandler<Employee>(Employee.class));
        System.out.println("所有的员工信息：");
        for (Employee e : employees){
            System.out.println(e.toString());
        }

        System.out.println("--------------------------------------");

        //需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
        String sql2 = "SELECT e.name, e.salary, did FROM employee AS e INNER JOIN dept AS d ON e.did = d.id ";
        List<Employee> employeeList = queryRunner.query(sql2, new BeanListHandler<Employee>(Employee.class));
        System.out.println("每个员工的 姓名, 薪资 和 所属部门名称:");
        for (Employee e : employeeList){
            String sql3 = "SELECT deptname FROM dept WHERE id = ?";
            String name = queryRunner.query(sql3, new ScalarHandler<String>(), e.getDid());
            System.out.println(e.getName()+"的薪资为"+e.getSalary()+",所属"+name);
        }

    }
}
